<?php

require_once "BCrypt.php";

require_once "Doctrine/DBAL/Configuration.php";
require_once "Doctrine/Common/ClassLoader.php";

require_once "types/Article.php";
require_once "types/Category.php";
require_once "types/CategorySection.php";
require_once "types/Section.php";
require_once "types/User.php";

/**
 * @author A.J. Janssen
 */
use Doctrine\Common\ClassLoader;
use Doctrine\DBAL\Configuration;
use Doctrine\DBAL\DriverManager;

//Compare articles by position
function cmpArticle($a, $b)
{
    return $a->position - $b->position;
}

//Compare categories by name
function cmpCategory($a, $b)
{
    return $a->position - $b->position;
}

class DataBaseController 
{
    /** @var Bcrypt */
    private $bcrypt;
    private $conn;
    
    function __construct()
    {
        $this->bcrypt = new Bcrypt(12);
        $classLoader = new ClassLoader('Doctrine', 'includes/database');
        $classLoader->register();
        $config = new Configuration();
        $connectionParams = array(
            'dbname' => 'acbdb',
            'user' => 'root',
            'password' => '',
            'host' => 'localhost',
            'driver' => 'pdo_mysql',
        );
        $this->conn = DriverManager::getConnection($connectionParams, $config);
        $this->initDB();
    }

    private function initDB()
    {
        if (!$this->conn || !$this->conn->connect())
        {
            throw new Exception("Could not connect to database");
        }
    }
    
    public function addUser($username, $email, $password)
    {
        if (empty($username) || empty($password))
            throw new Exception("Username of password was not provided");
        
        $sql = "INSERT INTO users (username, email, password) VALUES (:username, :email, :password)";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindValue("username", $username);
        $stmt->bindValue("email", $email);
        $stmt->bindValue("password", $this->bcrypt->hash($password));
        $stmt->execute();
    }
    
    public function verifyLogin($username, $password)
    {
        $sql = "SELECT username, password FROM users WHERE username = :username";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindValue("username", $username);
        $stmt->execute();
        $result = $stmt->fetch();
        if (!$result || !$this->bcrypt->verify($password, $result['password']))
            return false;
        return $result['username'];
    }
    
    public function getUsernameByEmail($email)
    {
        $sql = "SELECT username FROM users WHERE email = :email";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindValue("email", $email);
        $stmt->execute();
        $result = $stmt->fetchColumn();
        if (!$result)
            return FALSE;
        return $result;
    }
    
    public function userExists($username)
    {
        $sql = "SELECT * from users where username = :username";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindValue("username", $username);
        $stmt->execute();
        $result = $stmt->fetch();
        if (empty($result)) //User did not exist
            return false;
        //User exists, return as proper object
        $user = new User;
        foreach ($result as $key => $value)
            $user->{$key} = $value;
        return $user;
    }
    
    public function getCategorySections($categoryId)
    {
        $sql = "SELECT * FROM sections WHERE id IN(SELECT sectionid FROM categorysections WHERE categoryid = :categoryid)";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindValue("categoryid", $categoryId);
        $stmt->execute();
        $result = $stmt->fetchAll();
        //Convert to proper objects
        $sections = array();
        foreach ($result as $row => $values)
        {
            $section = new Section;
            foreach ($values as $key => $value) 
                $section->{$key} = $value;
            $sections[$section->id] = $section;
        }
        //return array with objects
        return $sections;
    }
    
    public function getSection($id)
    {
        //Retrieve data from database
        $sql = "SELECT * from sections where id = :id";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindValue("id", $id);
        $stmt->execute();
        $result = $stmt->fetchAll();
        if ($result)
        {
            //Convert to proper object
            $section = new Section;
            foreach ($result as $row => $values)
                foreach ($values as $key => $value) 
                {
                    $section->{$key} = $value;
                }
            return $section;
        }
        return null;
    }
    
    public function getSections()
    {
        //Retrieve data from database
        $sql = "SELECT * from sections";
        $stmt = $this->conn->prepare($sql);
        $stmt->execute();
        $result = $stmt->fetchAll();
        //Convert to proper objects
        $sections = array();
        foreach ($result as $row => $values)
        {
            $section = new Section;
            foreach ($values as $key => $value) 
                $section->{$key} = $value;
            $sections[$section->id] = $section;
        }
        //return array with objects
        return $sections;
    }
    
    public function getAllCategories()
    {
        //Retrieve data from database
        $sql = "SELECT * from categories";
        $stmt = $this->conn->prepare($sql);
        $stmt->execute();
        $result = $stmt->fetchAll();
        //Convert to proper objects
        $categories = array();
        foreach ($result as $row => $values)
        {
            $category = new Category;
            foreach ($values as $key => $value) 
                $category->{$key} = $value;
            if (empty($category->parentid))
                $category->parentid = 0;
            $categories[$category->id] = $category;
        }
        //return array with objects
        return $categories;
    }
    
    public function getBasicInfoArticles()
    {
        //Retrieve data from database
        $sql = "SELECT id, subject, parentid, position from articles";
        $stmt = $this->conn->prepare($sql);
        $stmt->execute();
        $result = $stmt->fetchAll();
        //Convert to proper objects
        $articles = array();
        foreach ($result as $row => $values)
        {
            $article = new Article;
            foreach ($values as $key => $value) 
                $article->{$key} = $value;
            $articles[$article->id] = $article;
        }
        //return array with objects
        return $articles;
    }
    
    public function getAllArticles()
    {
        //Retrieve data from database
        $sql = "SELECT * from articles";
        $stmt = $this->conn->prepare($sql);
        $stmt->execute();
        $result = $stmt->fetchAll();
        //Convert to proper objects
        $articles = array();
        foreach ($result as $row => $values)
        {
            $article = new Article;
            foreach ($values as $key => $value) 
                $article->{$key} = $value;
            $articles[$article->id] = $article;
        }
        //return array with objects
        return $articles;
    }
    
    public function getArticles($categoryId)
    {
        $sql = "SELECT * from articles WHERE parentid = :categoryId";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindValue("categoryId", $categoryId);
        $stmt->execute();
        $result = $stmt->fetchAll();
        //Convert to proper objects
        $articles = array();
        foreach ($result as $row => $values)
        {
            $article = new Article;
            foreach ($values as $key => $value) 
                $article->{$key} = $value;
            $articles[$article->id] = $article;
        }
        //return array with objects
        return $articles;
    }
    
    public function getArticle($id)
    {
        //Retrieve data from database
        $sql = "SELECT * from articles where id = :id";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindValue("id", $id);
        $stmt->execute();
        $result = $stmt->fetchAll();
        //Convert to proper object
        $article = new Article;
        foreach ($result as $row => $values)
            foreach ($values as $key => $value) 
                $article->{$key} = $value;
        //return user object
        return $article;
    }
    
    public function getCategory($id)
    {
        //Retrieve data from database
        $sql = "SELECT * from categories where id = :id";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindValue("id", $id);
        $stmt->execute();
        $result = $stmt->fetchAll();
        //Convert to proper object
        $category = new Category;
        foreach ($result as $row => $values)
            foreach ($values as $key => $value) 
                $category->{$key} = $value;
        //return user object
        return $category;
    }
    
    public function getCategories($parentId)
    {
        //Retrieve data from database
        $sql = NULL; $stmt = NULL;
        if ($parentId == NULL)
        {
            $sql = "SELECT * from categories where parentid IS NULL";
            $stmt = $this->conn->query($sql);
        }
        else
        {
            $sql = "SELECT * from categories where parentid = :parentid";
            $stmt = $this->conn->prepare($sql);
            $stmt->bindValue("parentid", $parentId);
            $stmt->execute();
        }
        $result = $stmt->fetchAll();
        //Convert to proper objects
        $categories = array();
        foreach ($result as $row => $values)
        {
            $category = new Category;
            foreach ($values as $key => $value) 
                $category->{$key} = $value;
            $categories[$category->id] = $category;
        }
        //return array with objects
        return $categories;
    }
    
    public function getUsers()
    {
        //Retrieve data from database
        $sql = "SELECT * from users";
        $stmt = $this->conn->prepare($sql);
        $stmt->execute();
        $result = $stmt->fetchAll();
        //Convert to proper objects
        $users = array();
        foreach ($result as $row => $values)
        {
            $user = new User;
            foreach ($values as $key => $value) 
                $user->{$key} = $value;
            $users[$user->id] = $user;
        }
        //return array with objects
        return $users;
    }
    
    public function getUserData($username)
    {
        //Retrieve data from database
        $sql = "SELECT * from users where username = :username";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindValue("username", $username);
        $stmt->execute();
        $result = $stmt->fetchAll();
        //Convert to proper object
        $user = new User;
        foreach ($result as $row => $values)
            foreach ($values as $key => $value) 
            {
                $user->{$key} = $value;
            }
        //return user object
        return $user;
    }
    
    /**
     *
     * @param Article $article 
     */
    public function postArticle($article)
    {
        $position = $this->getNextPosition($article->parentid);
        $result = $this->conn->insert("articles", array(
            "author" => $article->authorid, 
            "subject" => $article->subject, 
            "text" => $article->text,
            "parentid" => $article->parentid,
            "position" => $position));
        return $this->conn->lastInsertId();
    }
    
    public function getNextPosition($parentId, $fromCategories = false)
    {
        $sql = $fromCategories ?    "SELECT MAX(position) as position from categories where parentid = :parentid" : 
                                    "SELECT MAX(position) as position from articles where parentid = :parentid";
        $stmt = $this->conn->prepare($sql);
        if ($parentId == null)
            $stmt->bindValue("parentid", "NULL");
        else
            $stmt->bindValue("parentid", $parentId);
        $stmt->execute();
        $result = $stmt->fetchAll();
        if ($result[0]['position'] == null)
            return 1;
        return $result[0]['position'] + 1;
    }
    
    public function editArticle($article)
    {
        $result = $this->conn->update("articles", array(
            "subject" => $article->subject, 
            "text" => $article->text,
            "parentid" => $article->parentid),  array('id' => $article->id));
    }
    
    public function editArticlePosition($article)
    {
        $result = $this->conn->update("articles", array(
            "position" => $article->position),  array('id' => $article->id));
    }
    
    public function editCategoryPosition($category)
    {
        $result = $this->conn->update("categories", array(
            "position" => $category->position),  array('id' => $category->id));
    }
    
    public function editSection($section)
    {
        $result = $this->conn->update("sections", array(
            "name" => $section->name, 
            "description" => $section->description),  array('id' => $section->id));
    }
    
    public function deleteArticle($id)
    {
        //Get article
        $article = $this->getArticle($id);
        //Delete article from database
        $this->conn->delete('articles', array(
            'id' => $id));
        //Update position of remaining articles
        $sql = "UPDATE articles SET position = (position - 1) WHERE parentid = :parentid AND position > :position";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindValue("parentid", $article->parentid);
        $stmt->bindValue("position", $article->position);
        $stmt->execute();
    }
    
    public function renameCategory($id, $name)
    {
        $result = $this->conn->update('categories', array('name' => $name), array('id' => $id));
    }
    
    public function addCategory($parentId, $name)
    {
        $categoryData = array(
            "name" => $name);
        if ($parentId != null)
            $categoryData["parentId"] = $parentId;
        $categoryData["position"] = $this->getNextPosition($parentId, true);
        $result = $this->conn->insert('categories', $categoryData);
    }
    
    public function recursiveCategoryDelete($id)
    {   
        //Delete articles in this category
        $sql = "DELETE from articles WHERE parentid = :id";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindValue("id", $id);
        $stmt->execute();
        
        //Get category by id
        $category = $this->getCategory($id);
        //Remove from database
        $this->conn->delete('categories', array('id' => $id));
        //Update position of remaining categories with same parent
        $sql = "UPDATE categories SET position = (position - 1) WHERE parentid = :parentid AND position > :position";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindValue("parentid", $category->parentid);
        $stmt->bindValue("position", $category->position);
        $stmt->execute();
        
        //Collect orphans
        $sql = "SELECT id from categories WHERE parentid = :id";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindValue("id", $id);
        $stmt->execute();
        $result = $stmt->fetchAll();
        
        //Recurse with every orphan
        for ($i = 0; $i < count($result); $i++)
            $this->recursiveCategoryDelete($result[$i]['id']);
    }
    
    public function moveDown($element, $isCategory = false)
    {
        //Get list of all elements with the same parent
        $elements = null;
        if ($isCategory)
            $elements = $this->getCategories($element->parentid);
        else
            $elements = $this->getArticles($element->parentid);
        //Sort by position
        usort($elements, "cmpArticle"); //note that this may also be used to compare categories
        //Get last element
        $lastElement = end($elements); reset($elements); //Reset internal pointer
        if (count($elements) < 2 || $lastElement->id == $element->id) //If article is already last, do nothing
            return;
        //Else, swap position with next element
        foreach ($elements as $nextElement)
        {
            if ($nextElement->position == $element->position + 1)
            {
                //Found element, set new positions
                $nextElement->position = $element->position;
                $element->position = $element->position + 1;
                //Update in database
                if ($isCategory)
                {
                    $this->editCategoryPosition($element);
                    $this->editCategoryPosition($nextElement);
                    return; //stop here
                }
                $this->editArticlePosition($element);
                $this->editArticlePosition($nextElement);
                return; //stop here
            }
        }
    }
    
    public function moveUp($element, $isCategory = false)
    {
        //Get list of all elements with the same parent
        $elements = null;
        if ($isCategory)
            $elements = $this->getCategories($element->parentid);
        else
            $elements = $this->getArticles($element->parentid);
        //Sort by position
        usort($elements, "cmpArticle"); //note that this may also be used to compare categories
        //Get first elements
        $firstElement = reset($elements);
        if (count($elements) < 2 || $firstElement->id === $element->id) //If element is already first, do nothing
            return;
        //Else, swap position with previous element
        foreach ($elements as $previousElement)
        {
            if ($previousElement->position == $element->position - 1)
            {
                //Found element, set new positions
                $previousElement->position = $element->position;
                $element->position = $element->position - 1;
                //Update in database
                if ($isCategory)
                {
                    $this->editCategoryPosition($element);
                    $this->editCategoryPosition($previousElement);
                    return; //stop here
                }
                $this->editArticlePosition($element);
                $this->editArticlePosition($previousElement);
                return;
            }
        }
    }
    
    public function getAssignedSection($categoryid)
    {
        //Retrieve data from database
        $sql = "SELECT sectionid from categorysections where categoryid = :categoryid";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindValue("categoryid", $categoryid);
        $stmt->execute();
        $result = $stmt->fetchAll();
        if ($result[0]['sectionid'] == null)
            return null;
        return $result[0]['sectionid'];
    }
    
    public function assignToSection($categoryid, $sectionid)
    {
        //Check if category is already assigned to some section
        $currentSectionId = $this->getAssignedSection($categoryid);
        if ($currentSectionId != null && $currentSectionId == $sectionid)
            return; //Already assigned to this section, do nothing
        else if ($currentSectionId != null)
        {
            //Already assigned to (at least) one different section, remove previous assignment(s)
            $this->conn->delete('categorysections', array('categoryid' => $categoryid));
        }
        //Add new assignment
        $result = $this->conn->insert('categorysections', array(
            "categoryid" => $categoryid, 
            "sectionid" => $sectionid));
    }
    
    public function getAssignedMainCategories($sectionid)
    {
        //Retrieve data from database
        $sql = "SELECT categoryid from categorysections where sectionid = :sectionid";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindValue("sectionid", $sectionid);
        $stmt->execute();
        $result = $stmt->fetchAll();
        $categories = array();
        foreach ($result as $key => $value)
            $categories[] = $this->getCategory ($value['categoryid']);
        return $categories;
    }
    
    public function getUnassignedMainCategories($sectionid)
    {
        //Retrieve data from database
        $sql = "SELECT categoryid from categorysections where sectionid != :sectionid";
        $stmt = $this->conn->prepare($sql);
        $stmt->bindValue("sectionid", $sectionid);
        $stmt->execute();
        $result = $stmt->fetchAll();
        $categories = array();
        foreach ($result as $key => $value)
        {
            $category = $this->getCategory($value['categoryid']);
            $categories[$category->id] = $category;
        }
        return $categories;
    }
    
    public function enrichMainCategoriesWithAssignedSections($categories)
    {
        $sql = "SELECT * FROM categorysections WHERE categoryid IN (SELECT categoryid FROM categories WHERE parentid IS NULL)";
        $stmt = $this->conn->prepare($sql);
        $stmt->execute();
        $result = $stmt->fetchAll();
        foreach ($result as $row => $values)
        {
            //Build CategorySection object
            $categorySection = new CategorySection;
            foreach ($values as $key => $value) 
                $categorySection->{$key} = $value;
            //Assign to categories
            $categories[$categorySection->categoryid]->sectionid = $categorySection->sectionid;
        }
    }
}

?>
